package com.example.ntier.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.ntier.model.entity.Employee;

public class EmployeeDAL extends AbstractDAL<Integer, Employee> {

	private final DepartmentDAL departmentDAL;

	public EmployeeDAL(DepartmentDAL departmentDAL) {
		super("EMPLOYEES");
		this.departmentDAL = departmentDAL;
	}

	@Override
	public boolean insert(Employee entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "INSERT INTO employees VALUES (EMP_SEQ.nextval, ?, ?, ?, ?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd, new String[] { "EMPLOYEE_ID" })) {
				statement.setString(1, entity.getLastName());
				statement.setString(2, entity.getFirstName());
				statement.setInt(3, entity.getDepartment().getId());
				if (entity.getManager() == null) {
					statement.setObject(4, null);
				} else {
					statement.setInt(4, entity.getManager().getId());
				}
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
						if (generatedKeys.next()) {
							entity.setId(generatedKeys.getInt(1));
						}
					}
				}
			}
		}
		return result;
	}

	@Override
	public boolean update(Employee entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "UPDATE employees SET last_name = ?, first_name = ?, department_number = ?, manager_id = ? WHERE employee_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1, entity.getLastName());
				statement.setString(2, entity.getFirstName());
				statement.setInt(3, entity.getDepartment().getId());
				if (entity.getManager() == null) {
					statement.setObject(4, null);
				} else {
					statement.setInt(4, entity.getManager().getId());
				}
				statement.setInt(5, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean delete(Employee entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "DELETE FROM employees WHERE employee_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public Employee findById(Integer id) throws SQLException {
		Employee employee = null;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "SELECT * FROM employees WHERE employee_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				try (ResultSet resultSet = statement.executeQuery()) {
					if (resultSet.next()) {
						employee = createEntity(resultSet);
					}
				}
			}
		}
		return employee;
	}

	@Override
	public List<Employee> getAll() throws SQLException {
		List<Employee> employees = new ArrayList<Employee>();
		try (Connection connection = createConnection()) {
			try (Statement statement = connection.createStatement()) {
				final String sqlCmd = "SELECT * FROM Employees";
				try (ResultSet resultSet = statement.executeQuery(sqlCmd)) {
					while (resultSet.next()) {
						employees.add(createEntity(resultSet));
					}
				}
			}
		}
		return employees;
	}

	@Override
	Employee createEntity(ResultSet resultSet) throws SQLException {
		Employee employee = new Employee();
		employee.setId(resultSet.getInt("employee_id"));
		employee.setLastName(resultSet.getString("last_name"));
		employee.setFirstName(resultSet.getString("first_name"));
		{
			// DepartmentDAL departmentDAL =
			// (DepartmentDAL)getDAL("DEPARTMENT_DAL");
			int departmentId = resultSet.getInt("department_number");
			employee.setDepartment(departmentDAL.findById(departmentId));
		}
		{
			Object manager_id = resultSet.getObject("manager_id");
			if (manager_id != null) {
				int managerId = Integer.valueOf(manager_id.toString());
				employee.setManager(findById(managerId));
			}
		}
		return employee;
	}
}
